package com.thti.mecool.client.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

public class DataBase extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "mecool.db";
	public static final int DATABASE_VERSION = 1;

	public DataBase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);

	} 

	/**
	 * 创建数据库
	 * 
	 * @param context
	 *            Content
	 * @param name
	 *            数据库名
	 * @param cursorFactory
	 *            游标工厂
	 * @param version
	 *            数据库版本号
	 */
	public DataBase(Context context, String name, CursorFactory cursorFactory,
			int version) {
		super(context, name, cursorFactory, version);
	}

	// 创建表
	@Override
	public void onCreate(SQLiteDatabase db) {
		// 升级下载
		db.execSQL("CREATE TABLE IF NOT EXISTS filedownlog (id integer primary key autoincrement, downpath varchar(100), threadid INTEGER, downlength INTEGER)");
		// 同步管理
		db.execSQL("CREATE TABLE IF NOT EXISTS synchro (id integer primary key autoincrement,tablename varchar(100),cid INTEGER,surfaceID INTEGER, url varchar(400),commend varchar(400))");
		db.execSQL("CREATE TABLE IF NOT EXISTS photo (id integer primary key autoincrement,path varchar(400))");
		// 沟通管理
		db.execSQL("CREATE TABLE communication(id integer primary key autoincrement,userId varchar(20),communicationId varchar(20),projId  varchar(20),projStoreId  varchar(20),title  varchar(100),type  varchar(100),contactName  varchar(100),contactPosition  varchar(100),contactPhoneOrOther  varchar(100),content  varchar(200),recordDate varchar(100),projName varchar(100),projStoreName varchar(100))");
		// 工作备忘
		db.execSQL("CREATE TABLE workmemo(id integer primary key autoincrement,userId varchar(20),memoId  varchar(20) UNIQUE,channelCode  varchar(20),content  varchar(300),channelName  varchar(50),recordDate DATETIME,page INTEGER)");
		// 计划
		db.execSQL("CREATE TABLE plan(id integer primary key autoincrement,userId varchar(20),planId  varchar(20),planName  varchar(100),isFlag  integer,isType integer,planTime varchar(20),status varchar(10),denyNotes varchar(200),changeNotes varchar(200),type varchar(50),version INTEGER(10),createTime DATETIME)");
		// 计划项目
		db.execSQL("CREATE TABLE project(id integer primary key autoincrement,userId varchar(20),projectId  varchar(20),projectName  varchar(100),planId  varchar(20),isType integer,version INTEGER(10),createTime DATETIME)");
		// 计划任务
		db.execSQL("CREATE TABLE task(id integer primary key autoincrement,userId varchar(20),taskId  varchar(20),taskName  varchar(100),projectId  varchar(20),planId varchar(20),startTime varchar(20),endTime varchar(20),storeId varchar(25),storeName varchar(50),isType varchar(20),isFinish INTEGER,isUpload INTEGER,version INTEGER)");
		// 获取服务器的项目、终端、任务列表
		db.execSQL("CREATE TABLE projectServer(id integer primary key autoincrement, projectId varchar(20),name  varchar(50),taskId  varchar(20),storeId varchar(20),isType integer,requiredCount integer,finishedCount integer,criticalJob integer)");

		db.execSQL("CREATE TABLE workmemoChannel(id integer primary key autoincrement, code varchar(100),name varchar(100))");

		db.execSQL("CREATE TABLE taskStep(id integer primary key autoincrement, planId varchar(20),projectId varchar(20),taskId varchar(20),stepId varchar(20),parentId varchar(20),parentStepId varchar(20),isType integer,actionId integer,name varchar(100),storeAddress varchar(50),data varchar(500),dataExtra  varchar(500),images  BLOB(65535),isUpload INTEGER,latitude varchar(50),longitude varchar(50),isFinsh INTEGER,extra1 varchar(300),extra2 varchar(300),extra3 varchar(300),isOver INTEGER,version INTEGER,createTime varchar(30))");
		// 卖进管理项目表
		db.execSQL("CREATE TABLE saleproject(id integer primary key autoincrement, userId varchar(20),projectId varchar(20),name  varchar(50),storeId varchar(20),updateTime varchar(20),isType varchar(20))");
		// 卖进管理终端表
		db.execSQL("CREATE TABLE salestore(id integer primary key autoincrement,storeId varchar(20),projectId varchar(20),name  varchar(50),provinceAdName varchar(100),provinceAdCode varchar(100),cityAdName varchar(100),cityAdCode varchar(100),status INTEGER,tgStockBaseDesc INTEGER,executionStatus INTEGER,problemType INTEGER,problemDescription varchar(300),description varchar(300),lastModified varchar(300))");
		// 卖进管理销售人员信息表
		db.execSQL("CREATE TABLE salers(id integer primary key autoincrement, storeId varchar(20),name varchar(50),phone varchar(50),idCardNo varchar(50),onDutyTime varchar(50),offTime varchar(50))");
		// 消息管理
		db.execSQL("create table messages(messageID INTEGER PRIMARY KEY AUTOINCREMENT,id varchar(20),head VARCHAR(50),body VARCHAR(500),priority VARCHAR(10),type varchar(10),createdOn varchar(20),flag INTEGER)");
	}

	@Override
	/**
	 * 新新版本
	 * @param SQLiteDatabase  数据库名 
	 * @param oldVersion      老数据库版本号
	 * @param newVersion      新数据库版本号
	 */
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	/**
	 * 打开数据库
	 */
	public boolean onCreate() {
		this.getWritableDatabase();
		// openDatabase();
		return true;
	}

	/**
	 * 插入新数据
	 * 
	 * @param tableName
	 *            表名
	 * @param values
	 *            返回数据包含的列信息
	 */
	public long insert(String tableName, ContentValues values) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.insert(tableName, null, values);
		return 0;
	}

	/**
	 * 
	 * @param tableName
	 *            表名
	 * @param projection
	 *            返回数据包含的列信息，String数组里放的都是列名
	 * @param selection
	 *            相当于sql里的where，sql里where后写的内容，例如：tage>?
	 * @param selectionArgs
	 *            如果你在参数3里写了?（知道我为什么写tage>?了吧），那个这里就是代替?的值 接上例：new String[]{"30
	 * @param groupBy
	 *            分组
	 * @param having
	 *            having
	 * @param sortOrder
	 *            orderBy排序
	 * @return
	 */
	public Cursor query(String tableName, String[] projection,
			String selection, String[] selectionArgs, String groupBy,
			String having, String sortOrder) {
		// TODO Auto-generated method stub
		SQLiteDatabase db = this.getWritableDatabase();
		SQLiteQueryBuilder sb = new SQLiteQueryBuilder();
		sb.setTables(tableName);
		Cursor c = sb.query(db, projection, selection, selectionArgs, groupBy,
				having, sortOrder);

		return c;
	}

	/**
	 * 数据更新
	 * 
	 * @param tableName
	 *            表名
	 * @param values
	 *            返回数据包含的列信息
	 * @param selection
	 *            相当于sql里的where，sql里where后写的内容
	 */
	public int update(String tableName, ContentValues values, String selection,
			String[] selectionArgs) {
		// TODO Auto-generated method stub
		try {
			SQLiteDatabase db = this.getWritableDatabase();
			db.update(tableName, values, selection, selectionArgs);
			db.close();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 删除数据
	 * 
	 * @param tableName
	 *            表名
	 * @param selection
	 *            相当于sql里的where，sql里where后写的内容
	 */
	public int delete(String tableName, String selection, String[] selectionArgs) {
		// TODO Auto-generated method stub
		SQLiteDatabase db = this.getWritableDatabase();
		int d = db.delete(tableName, selection, selectionArgs);
		db.close();
		return d;
	}

	/**
	 * 自定义查询数据
	 * 
	 * @param sql
	 * @return
	 */
	public Cursor queryObject(String sql, String[] selectionArgs) {
		SQLiteDatabase db = this.getWritableDatabase();
		return db.rawQuery(sql, selectionArgs);
	}

	/**
	 * 自定义增删改数据
	 */
	public boolean execObject(String sql, Object[] selectionArgs) {
		SQLiteDatabase db = this.getWritableDatabase();
		// db.beginTransaction();
		try {
			db.execSQL(sql, selectionArgs);
			db.close();
			// db.setTransactionSuccessful();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
		// db.endTransaction();
	}

	/**
	 * 事务
	 */
	public void execTransaction(ArrayList<HashMap<String, String[]>> args) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.beginTransaction();// 开始事务
		try {
			for (HashMap<String, String[]> arg : args) {
				Iterator<String> it = arg.keySet().iterator();
				for (; it.hasNext();) {
					String sql = it.next();
					db.execSQL(sql, arg.get(sql));
				}
			}
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		db.close();
	}

	/**
	 * 查询总数
	 * 
	 * @param sql
	 * @return
	 */
	public long getCount(String sql, String[] selectionArgs) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor c = db.rawQuery(sql, selectionArgs);
		return c.getLong(0);

	}

	/**
	 * 删除数据表
	 * 
	 * @param tableName
	 */
	@SuppressWarnings("unused")
	private void dropTable(String tableName) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "DROP TABLE IF EXISTS " + tableName;
		try {
			db.execSQL(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 查询所有数据
	 * 
	 * @param cls
	 *            加载类
	 * @param sql
	 *            查询sql
	 * @param selectionArgs
	 *            参数
	 * @return List对象
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List findAll(Class cls, String sql, String[] selectionArgs) {
		List list = new ArrayList();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, selectionArgs);
		if (cursor != null) {
			try {
				while (cursor.moveToNext()) {
					Object o = cls.newInstance();
					Field[] fields = cls.getDeclaredFields();
					for (Field field : fields) {
						String fieldName = field.getName();
						if (fieldName.equals("serialVersionUID")
								|| cursor.getColumnIndex(fieldName) == -1) {
							continue;
						}
						Method method = cls.getDeclaredMethod("set"
								+ fieldName.substring(0, 1).toUpperCase()
								+ fieldName.substring(1),
								new Class[] { field.getType() });

						if (field.getType().toString().equals("int")
								|| field.getType().toString()
										.endsWith("Integer")) {
							method.invoke(o, cursor.getInt(cursor
									.getColumnIndex(fieldName)));
						} else if (field.getType().toString()
								.endsWith("String")) {
							method.invoke(o, cursor.getString(cursor
									.getColumnIndex(fieldName)));
						} else if (field.getType().isArray()) {
							method.invoke(o, cursor.getBlob(cursor
									.getColumnIndex(fieldName)));
						}else if (field.getType().toString().endsWith("Long")) {
							method.invoke(o, cursor.getLong(cursor
									.getColumnIndex(fieldName)));
						}
					}
					list.add(o);
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			} finally {
				cursor.close();
				db.close();
			}
		}

		return list;
	}

	/**
	 * 
	 * @param cls
	 *            加载类
	 * @param sql
	 *            查询sql
	 * @param selectionArgs
	 *            参数
	 * @return 返回Object对象
	 */
	@SuppressWarnings({ "rawtypes" })
	public Object findByObject(Class cls, String sql, String[] selectionArgs) {
		Object o = null;
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			o = cls.newInstance();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Cursor cursor = db.rawQuery(sql, selectionArgs);
		if (cursor != null) {
			try {
				while (cursor.moveToNext()) {
					Field[] fields = cls.getDeclaredFields();
					for (Field field : fields) {
						String fieldName = field.getName();
						if (fieldName.equals("serialVersionUID")
								|| cursor.getColumnIndex(fieldName) == -1) {
							continue;
						}
						Method method = cls.getDeclaredMethod("set"
								+ fieldName.substring(0, 1).toUpperCase()
								+ fieldName.substring(1),
								new Class[] { field.getType() });
						if (field.getType().toString().equals("int")
								|| field.getType().toString()
										.endsWith("Integer")) {
							method.invoke(o, cursor.getInt(cursor
									.getColumnIndex(fieldName)));
						} else if (field.getType().toString()
								.endsWith("String")) {
							method.invoke(o, cursor.getString(cursor
									.getColumnIndex(fieldName)));
						}
					}
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			} finally {
				cursor.close();
				db.close();
			}
		}
		return o;

	}

}
